/**
 * SQLite 实现
 */
import { BaseDatabase, DatabaseConfig, QueryOptions, QueryResult } from './base';
import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

export class SQLiteDatabase extends BaseDatabase {
  private db?: Database.Database;

  constructor(config: DatabaseConfig) {
    super(config);
  }

  async connect(): Promise<void> {
    if (this.connected) return;
    const dbPath = this.config.connectionString || path.join(process.cwd(), 'data', 'app.sqlite');
    // 确保目录存在
    const dir = path.dirname(dbPath);
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
    }
    // 由 better-sqlite3 自动创建文件
    this.db = new Database(dbPath);
    // 开启WAL模式提升并发
    this.db.pragma('journal_mode = WAL');
    this.connected = true;
  }

  async disconnect(): Promise<void> {
    if (this.db) {
      this.db.close();
      this.db = undefined;
    }
    this.connected = false;
  }

  isConnected(): boolean { return this.connected; }

  private ensureTable(collection: string, sample: Record<string, any> = {}): void {
    if (!this.db) throw new Error('SQLite not connected');
    // 简单的KV风格：id TEXT PRIMARY KEY, data JSON, created_at, updated_at
    const createStmt = `CREATE TABLE IF NOT EXISTS ${collection} (
      id TEXT PRIMARY KEY,
      data TEXT NOT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )`;
    this.db.prepare(createStmt).run();
    // 创建索引
    this.db.prepare(`CREATE INDEX IF NOT EXISTS idx_${collection}_created_at ON ${collection}(created_at)`).run();
    this.db.prepare(`CREATE INDEX IF NOT EXISTS idx_${collection}_updated_at ON ${collection}(updated_at)`).run();
  }

  private buildWhere(filter: Record<string, any> = {}): { clause: string; params: any[] } {
    const keys = Object.keys(filter || {});
    if (keys.length === 0) return { clause: '', params: [] };
    const parts: string[] = [];
    const params: any[] = [];
    for (const k of keys) {
      parts.push(`json_extract(data, '$.${k}') = ?`);
      params.push(filter[k]);
    }
    return { clause: 'WHERE ' + parts.join(' AND '), params };
  }

  async create<T>(collection: string, data: Partial<T>): Promise<QueryResult<T>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const id = (data as any).id || crypto.randomUUID();
      const now = new Date().toISOString();
      const stmt = this.db!.prepare(`INSERT INTO ${collection} (id, data, created_at, updated_at) VALUES (?, ?, ?, ?)`);
      stmt.run(id, JSON.stringify({ ...data, id }), now, now);
      return { success: true, data: { ...(data as any), id } };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async createMany<T>(collection: string, data: Partial<T>[]): Promise<QueryResult<T[]>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const now = new Date().toISOString();
      const stmt = this.db!.prepare(`INSERT INTO ${collection} (id, data, created_at, updated_at) VALUES (?, ?, ?, ?)`);
      const tx = this.db!.transaction((rows: Partial<T>[]) => {
        const res: any[] = [];
        for (const row of rows) {
          const id = (row as any).id || crypto.randomUUID();
          stmt.run(id, JSON.stringify({ ...row, id }), now, now);
          res.push({ ...(row as any), id });
        }
        return res;
      });
      const inserted = tx(data);
      return { success: true, data: inserted };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async findOne<T>(collection: string, filter: Record<string, any>): Promise<QueryResult<T>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const { clause, params } = this.buildWhere(filter);
      const row = this.db!.prepare(`SELECT data FROM ${collection} ${clause} LIMIT 1`).get(...params) as any;
      if (!row) return { success: true, data: undefined as any };
      return { success: true, data: JSON.parse(row.data) };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async findMany<T>(collection: string, filter: Record<string, any> = {}, options: QueryOptions = {}): Promise<QueryResult<T[]>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const page = Math.max(1, options.page || 1);
      const pageSize = Math.min(100, Math.max(1, options.pageSize || 20));
      const { clause, params } = this.buildWhere(filter);
      // sortBy 为 data 内字段
      const sortBy = options.sortBy ? `json_extract(data, '$.${options.sortBy}')` : 'created_at';
      const sortOrder = options.sortOrder === 'asc' ? 'ASC' : 'DESC';
      const offset = (page - 1) * pageSize;
      const stmt = this.db!.prepare(`SELECT data FROM ${collection} ${clause} ORDER BY ${sortBy} ${sortOrder} LIMIT ? OFFSET ?`);
      const rows = stmt.all(...params, pageSize, offset) as any[];
      return { success: true, data: rows.map(r => JSON.parse(r.data)) };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async update<T>(collection: string, filter: Record<string, any>, data: Partial<T>): Promise<QueryResult<T>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const found = await this.findOne<T>(collection, filter);
      if (!found.data) return { success: false, error: 'Not Found' };
      const merged = { ...(found.data as any), ...data };
      const now = new Date().toISOString();
      const stmt = this.db!.prepare(`UPDATE ${collection} SET data = ?, updated_at = ? WHERE id = ?`);
      stmt.run(JSON.stringify(merged), now, (found.data as any).id);
      return { success: true, data: merged };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async updateMany<T>(collection: string, filter: Record<string, any>, data: Partial<T>): Promise<QueryResult<number>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const all = await this.findMany<T>(collection, filter, { page: 1, pageSize: 10000 });
      let count = 0;
      for (const row of all.data || []) {
        const merged = { ...(row as any), ...data };
        const now = new Date().toISOString();
        this.db!.prepare(`UPDATE ${collection} SET data = ?, updated_at = ? WHERE id = ?`)?.run(JSON.stringify(merged), now, (row as any).id);
        count++;
      }
      return { success: true, data: count };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async delete(collection: string, filter: Record<string, any>): Promise<QueryResult<boolean>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const found = await this.findOne<any>(collection, filter);
      if (!found.data) return { success: false, error: 'Not Found' };
      this.db!.prepare(`DELETE FROM ${collection} WHERE id = ?`).run((found.data as any).id);
      return { success: true, data: true };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async deleteMany(collection: string, filter: Record<string, any>): Promise<QueryResult<number>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const all = await this.findMany<any>(collection, filter, { page: 1, pageSize: 10000 });
      let count = 0;
      for (const row of all.data || []) {
        this.db!.prepare(`DELETE FROM ${collection} WHERE id = ?`).run((row as any).id);
        count++;
      }
      return { success: true, data: count };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async count(collection: string, filter: Record<string, any> = {}): Promise<QueryResult<number>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      const { clause, params } = this.buildWhere(filter);
      const row = this.db!.prepare(`SELECT COUNT(1) as cnt FROM ${collection} ${clause}`).get(...params) as any;
      return { success: true, data: row?.cnt || 0 };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async createIndex(collection: string, fields: Record<string, any>): Promise<QueryResult<boolean>> {
    try {
      if (!this.db) await this.connect();
      this.ensureTable(collection);
      // SQLite 对 JSON 索引支持有限，这里跳过或创建常用字段索引
      return { success: true, data: true };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async dropCollection(collection: string): Promise<QueryResult<boolean>> {
    try {
      if (!this.db) await this.connect();
      this.db!.prepare(`DROP TABLE IF EXISTS ${collection}`).run();
      return { success: true, data: true };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }

  async listCollections(): Promise<QueryResult<string[]>> {
    try {
      if (!this.db) await this.connect();
      const rows = this.db!.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'").all() as any[];
      return { success: true, data: rows.map(r => r.name) };
    } catch (e: any) {
      return { success: false, error: e.message };
    }
  }
}

export default SQLiteDatabase;